package com.lyouu.dataminer.bustour;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import org.apache.log4j.Logger;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.db.DbLyouuParams;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.CategorizerUtil;
import com.lyouu.dataminer.util.Constants;
import com.lyouu.dataminer.util.TourMap;
import com.lyouu.dataminer.util.Util;

public class BTCaissaDe {
	private static final String CURRENT_YEAR = String.valueOf(Calendar
			.getInstance().get(Calendar.YEAR));
	private Logger logger = Logger.getLogger(BTCaissaDe.class);
	private Connection conn;
	private String currency;
	// lyouu_src db, bt_caissa_de_brief table
	private ArrayList<String> bPageUrlList;
	private ArrayList<String> bAreaList;
	private ArrayList<String> bDepartCityList;
	private ArrayList<String> bContentUrlList;
	// lyouu_src db, bt_caissa_de_content table
	private ArrayList<String> cTitleList;
	private ArrayList<String> cPageUrlList;
	private ArrayList<String> cRouteList;
	private ArrayList<String> cHighlightList;
	private ArrayList<String> cScheduleList;
	private ArrayList<String> cGroupIdList;
	private ArrayList<String> cTravelDateList;
	private ArrayList<String> cPriceList;
	private ArrayList<String> cStatusList;
	private ArrayList<String> cOrderUrlList;

	private String cruiserContentUrlStr;
	private ArrayList<TourBrief> tourBriefList;

	public BTCaissaDe() {
		this.bPageUrlList = new ArrayList<String>();// for test
		this.bAreaList = new ArrayList<String>();
		this.bDepartCityList = new ArrayList<String>();
		this.bContentUrlList = new ArrayList<String>();
		this.cTitleList = new ArrayList<String>();
		this.cPageUrlList = new ArrayList<String>();
		this.cRouteList = new ArrayList<String>();
		this.cHighlightList = new ArrayList<String>();
		this.cScheduleList = new ArrayList<String>();
		this.cGroupIdList = new ArrayList<String>();
		this.cTravelDateList = new ArrayList<String>();
		this.cPriceList = new ArrayList<String>();
		this.cStatusList = new ArrayList<String>();
		this.cOrderUrlList = new ArrayList<String>();
	}

	public void getData() throws SQLException {
		conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU_SRC);
		String sql = "SELECT * FROM bt_caissa_de_brief";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			if (rs.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL).equals(
					"http://zh.caissa.de/index.php/Category/index/id/3")) {
				cruiserContentUrlStr = rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_CONTENT_URL);
			}
			if (!rs.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL).equals(
					"http://zh.caissa.de/index.php/Category/index/id/3")) {
				bPageUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL));
				bAreaList
						.add(rs.getString(DbConstants.LYOUU_SRC_BUS_TOUR_AREA));
				bDepartCityList
						.add(Util
								.replaceHtmlCodeToSpecialCharacter(formatDepartCityStr(rs
										.getString(DbConstants.LYOUU_SRC_BUS_TOUR_DEPART_CITY))));
				bContentUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_CONTENT_URL));
			}
		}
		tourBriefList = generateTourBriefList(bPageUrlList, bAreaList,
				bContentUrlList, bDepartCityList);
	
		sql = "SELECT * FROM bt_caissa_de_content";
		rs = stmt.executeQuery(sql);
		boolean isCruiserPageUrl;
		String[] cruiserContentUrlAry = cruiserContentUrlStr
				.split(Constants.DELIMITER);
		while (rs.next()) {
			isCruiserPageUrl = false;
			for (String cruiserContentUrl : cruiserContentUrlAry) {
				if (cruiserContentUrl.equals(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL))) {
					isCruiserPageUrl = true;
					break;
				}
			}
			if (!isCruiserPageUrl) {
				cTitleList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_TITLE));
				cPageUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL));
				cRouteList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_ROUTE));
				cHighlightList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_HIGHLIGHT));
				cScheduleList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_SCHEDULE));
				cGroupIdList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_GROUP_ID));
				cTravelDateList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_TRAVEL_DATE));
				cPriceList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PRICE));
				cStatusList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_STATUS));
				cOrderUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_ORDER_URL));
			}
		}
		conn.close();
	}

	public void printData() {
		OutputStream os = null;
		try {
			os = new FileOutputStream("Z:\\Downloads\\bt_caissa_de.txt");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		final PrintStream ps = new PrintStream(os);

		ps.println("Tour Brief List:");
		for (int i = 0; i < tourBriefList.size(); i++) {
			ps.println(i + 1);
			ps.println(tourBriefList.get(i).getContentUrl());
			ps.println(tourBriefList.get(i).getTourArea());
			ps.println(tourBriefList.get(i).getDepartCity());
		}

		for (int i = 0; i < cTitleList.size(); i++) {
			ps.println(i + 1);
			ps.println((i + 1) + ":\t" + cTitleList.get(i) + "\t"
					+ cPageUrlList.get(i));
			ps.println(cRouteList.get(i));
			ps.println(cHighlightList.get(i));
			ps.println(cScheduleList.get(i));
			ps.println(cGroupIdList.get(i));
			ps.println(cTravelDateList.get(i));
			ps.println(cPriceList.get(i));
			ps.println(cStatusList.get(i));
			ps.println(cOrderUrlList.get(i));
			ps.println("\n\n");
		}
		ps.close();
		try {
			os.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateDbLyouu() throws SQLException {
		String name, content, departCity, routine, highlight, departCountry, tourArea;
		// bustour_schedule table
		ArrayList<String> btsGroupIdList = new ArrayList<String>();
		ArrayList<String> btsDepartDateList = new ArrayList<String>();
		ArrayList<String> btsEndDateList = new ArrayList<String>();
		ArrayList<String> btsAdultPriceList = new ArrayList<String>();
		ArrayList<String> btsChildPriceList = new ArrayList<String>();
		ArrayList<String> btsGroupPriceList = new ArrayList<String>();
		ArrayList<String> btsStatusList = new ArrayList<String>();
		ArrayList<String> btsBookingList = new ArrayList<String>();

		String tourAreaAttidStr;
		for (int i = 0; i < cTitleList.size(); i++) {
			name = parseName(cTitleList.get(i));
			logger.info("\n" + (i + 1) + ": " + name + "  "
					+ cPageUrlList.get(i));
			content = parseContent(cScheduleList.get(i));
			tourArea = "其它线路";
			departCity = "";
			for (int j = 0; j < tourBriefList.size(); j++) {
				if (cPageUrlList.get(i).equals(
						tourBriefList.get(j).getContentUrl())) {
					tourArea = tourBriefList.get(i).getTourArea();
					tourArea = tourArea.replace("线路", "");
					departCity = parseDepartCity(tourBriefList.get(i)
							.getDepartCity());
				}
			}
			routine = parseRoutine(cRouteList.get(i));
			highlight = parseHighlight(cHighlightList.get(i));
			departCountry = parseDepartCountry(departCity);
			tourAreaAttidStr = CategorizerUtil.getDefaultAreaAttId();// 其它线路
			tourAreaAttidStr = CategorizerUtil.selectTourArea(tourArea, name);
			parseGroupId(cGroupIdList.get(i), btsGroupIdList);
			this.currency = Constants.CURRENCY_EUR;
			parsePrice(tourAreaAttidStr, cPriceList.get(i), btsAdultPriceList,
					btsChildPriceList, btsGroupPriceList);
			parseTravelDate(cTravelDateList.get(i), btsDepartDateList,
					btsEndDateList);
			parseStatus(cStatusList.get(i), btsStatusList);
			parseBooking(btsGroupIdList.size(), cOrderUrlList.get(i),
					btsBookingList);
			// update db_lyouu

			BusTour.insertBustour(cPageUrlList.get(i),
					Constants.OWNER_DE_CAISSA, name, Constants.NO_THUMB,
					content, departCity, routine, this.currency,
					Util.getMinPrice(btsAdultPriceList),
					Constants.PROVIDER_DE_CAISSA,
					Util.getMinPrice(btsChildPriceList),
					Util.getMinPrice(btsGroupPriceList), departCountry,
					btsDepartDateList, btsEndDateList, highlight, tourArea);

			BusTour.insertBustourSchedule(Constants.OWNER_DE_CAISSA, name,
					btsGroupIdList, btsDepartDateList, btsEndDateList,
					this.currency, btsAdultPriceList, btsChildPriceList,
					btsGroupPriceList, btsStatusList, btsBookingList,
					cPageUrlList.get(i));
		}
	}

	private ArrayList<TourBrief> generateTourBriefList(
			ArrayList<String> bPageUrlList, ArrayList<String> areaList,
			ArrayList<String> contentUrlList, ArrayList<String> departCityList) {
		ArrayList<TourBrief> tbList = new ArrayList<TourBrief>();
		if (contentUrlList.size() == departCityList.size()) {
			String[] contentUrlAry;
			String[] departCityAry;
			for (int i = 0; i < contentUrlList.size(); i++) {
				contentUrlAry = contentUrlList.get(i)
						.split(Constants.DELIMITER);
				departCityAry = departCityList.get(i)
						.split(Constants.DELIMITER);
				logger.info(departCityList.get(i));

				// 预处理不包含上客城市的线路，设置上客城市为空值
				if (bPageUrlList.get(i).equals(
						"http://zh.caissa.de/index.php/Category/index/id/34")) {
					String[] tempAry = new String[] { departCityAry[0],
							departCityAry[1], "欧洲各地" };
					departCityAry = tempAry;
				} else if (bPageUrlList.get(i).equals(
						"http://zh.caissa.de/index.php/Category/index/id/36")) {
					String[] tempAry = new String[] {"欧洲各地",
							departCityAry[0], "英国各地" };
					departCityAry = tempAry;
				} else if (bPageUrlList.get(i).equals(
						"http://zh.caissa.de/index.php/Category/index/id/40")) {
					String[] tempAry = new String[contentUrlAry.length];
					for (int a = 0; a < contentUrlAry.length; a++)
						tempAry[a] = "英国各地";
					departCityAry = tempAry;
				} else if (bPageUrlList.get(i).equals(
						"http://zh.caissa.de/index.php/Category/index/id/1")) {
					String[] tempAry = new String[contentUrlAry.length];
					logger.info(contentUrlAry.length);
//					for (int a = 0; a < contentUrlAry.length; a++)
//						tempAry[a] = "英国各地";
					tempAry[0] = "欧洲各地";
//					tempAry[1] = "法兰克福，纽伦堡，慕尼黑";
//					tempAry[13] = "法兰克福，斯图加特";
//					tempAry[15] = "科隆，法兰克福，斯图加特，慕尼黑";
//					tempAry[16] = "法兰克福，纽伦堡，慕尼黑";
//					tempAry[17] = "科隆，法兰克福，斯图加特，慕尼黑";
//					tempAry[18] = "科隆，法兰克福，斯图加特，慕尼黑";
					departCityAry = tempAry;
				}

				if (contentUrlAry.length == departCityAry.length) {
					for (int j = 0; j < contentUrlAry.length; j++)
						tbList.add(new TourBrief(areaList.get(i),
								contentUrlAry[j], departCityAry[j]));
				} else {
					logger.info("\nThe length of "
							+ i
							+ "th contentUrlAry and departCityAry are NOT same.");
					logger.info(bPageUrlList.get(i));
					logger.info("length=" + contentUrlAry.length + "\n"
							+ contentUrlList.get(i));
					logger.info("length=" + departCityAry.length + "\n"
							+ departCityList.get(i));
				}
			}
		} else {
			logger.info("The size of contentUrlList and departCityList are NOT same.");
		}
		return tbList;
	}

	private String parseName(String str) {
		return str.trim();
	}

	private String parseContent(String str) {
		return str.trim();
	}

	private String parseRoutine(String str) {
		// logger.info(str);
		String pattern = "\\&nbsp;|\\s+|\\r|\n|\u3000|[\\s\\p{Zs}]";
		str = str.replaceAll(pattern, "");
		pattern = "第\\d+天";
		str = str.replaceAll(pattern, "");
		pattern = "\\(\\w+\\)";
		str = str.replaceAll(pattern, "");
		str = str.replace("--", "-");
		// logger.info(str);

		String[] routineAry = str.split("-");
		ArrayList<String> routineList = new ArrayList<String>();
		int i, j;
		String place;
		for (i = 0; i < routineAry.length; i++) {
			place = routineAry[i].trim();
			for (j = 0; j < routineList.size(); j++) {
				if (place.equalsIgnoreCase(routineList.get(j)))
					break;
			}
			if (j == routineList.size())
				routineList.add(place);
		}
		String resRoutine = "";
		for (i = 0; i < routineList.size() - 1; i++)
			resRoutine += routineList.get(i) + "-";
		resRoutine += routineList.get(i);
		// logger.info(resRoutine);
		return resRoutine;
	}

	private String parseHighlight(String str) {
		return str;
	}

	private String formatDepartCityStr(String str) {
		str = str.replaceAll("\\d", "");
		str = str.replaceAll("\\.", "");
		str = str.replace("Hbf", "");
		str = Util.replaceHtmlCodeToSpecialCharacter(str);
		str = str.trim();
		return str;
	}

	private String parseDepartCity(String str) {
		ArrayList<String> departCityList = TourMap.getInstance().retrieveDepartPlace(str);
		String departCities = "";
		if (!departCityList.isEmpty()) {
			int i = 0;
			for (; i < departCityList.size() - 1; i++)
				departCities += departCityList.get(i) + ", ";
			departCities += departCityList.get(i);
		}
		return departCities;
	}

	private String parseDepartCountry(String str) {
		return "64";
	}

	private void parsePrice(String tourAreaAttidStr, String str,
			ArrayList<String> adultPriceList, ArrayList<String> childPriceList,
			ArrayList<String> groupPriceList) {
		adultPriceList.clear();
		childPriceList.clear();
		groupPriceList.clear();
		str = str.replace(",", ".");
		str = str.replace("€", "");
		String[] priceStrAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < priceStrAry.length; i++) {
			adultPriceList.add(priceStrAry[i]);
			if (tourAreaAttidStr.contains("77")) {// 英国线路
				childPriceList.add(Util.formatPrice(Double
						.valueOf(priceStrAry[i]) * 0.5));
			} else {
				childPriceList.add(Util.formatPrice(Double
						.valueOf(priceStrAry[i]) * 0.6));
			}
			groupPriceList
					.add(Util.formatPrice(Double.valueOf(priceStrAry[i]) - 20));

		}
	}

	private void parseGroupId(String str, ArrayList<String> groupIdList) {
		groupIdList.clear();
		String[] groupIdAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < groupIdAry.length; i++) {
			groupIdList.add(groupIdAry[i].trim());
		}
	}

	private void parseTravelDate(String str, ArrayList<String> departDateList,
			ArrayList<String> endDateList) {
		departDateList.clear();
		endDateList.clear();
		String departDate;
		String endDate;
		String[] dateAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < dateAry.length; i++) {
			departDate = dateAry[i].split(" ")[0];
			endDate = dateAry[i].split(" ")[1];
			departDate = departDate.replaceAll("20\\d\\d-", "");
			departDate = departDate.replace("-", ".");
			endDate = endDate.replaceAll("20\\d\\d-", "");
			endDate = endDate.replace("-", ".");
			departDateList.add(departDate);
			endDateList.add(endDate);
		}
	}

	private void parseBooking(int size, String str,
			ArrayList<String> bookingList) {
		bookingList.clear();
		for (int i = 0; i < size; i++) {
			bookingList.add(str);
		}
	}

	private void parseStatus(String str, ArrayList<String> cStatusList) {
		cStatusList.clear();
		String[] statusStrAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < statusStrAry.length; i++) {
			cStatusList.add(statusStrAry[i].trim());
		}
	}

	public ArrayList<String> getbPageUrlList() {
		return bPageUrlList;
	}

	public void setbPageUrlList(ArrayList<String> bPageUrlList) {
		this.bPageUrlList = bPageUrlList;
	}

	public ArrayList<String> getbDepartCityList() {
		return bDepartCityList;
	}

	public void setbDepartCityList(ArrayList<String> bDepartCityList) {
		this.bDepartCityList = bDepartCityList;
	}

	public ArrayList<String> getbContentUrlList() {
		return bContentUrlList;
	}

	public void setbContentUrlList(ArrayList<String> bContentUrlList) {
		this.bContentUrlList = bContentUrlList;
	}

	public ArrayList<String> getcTitleList() {
		return cTitleList;
	}

	public void setcTitleList(ArrayList<String> cTitleList) {
		this.cTitleList = cTitleList;
	}

	public ArrayList<String> getcPageUrlList() {
		return cPageUrlList;
	}

	public void setcPageUrlList(ArrayList<String> cPageUrlList) {
		this.cPageUrlList = cPageUrlList;
	}

	public ArrayList<String> getcRouteList() {
		return cRouteList;
	}

	public void setcRouteList(ArrayList<String> cRouteList) {
		this.cRouteList = cRouteList;
	}

	public ArrayList<String> getcScheduleList() {
		return cScheduleList;
	}

	public void setcScheduleList(ArrayList<String> cScheduleList) {
		this.cScheduleList = cScheduleList;
	}

	public ArrayList<String> getcGroupIdList() {
		return cGroupIdList;
	}

	public void setcGroupIdList(ArrayList<String> cGroupIdList) {
		this.cGroupIdList = cGroupIdList;
	}

	public ArrayList<String> getcTravelDateList() {
		return cTravelDateList;
	}

	public void setcTravelDateList(ArrayList<String> cTravelDateList) {
		this.cTravelDateList = cTravelDateList;
	}

	public ArrayList<String> getcPriceList() {
		return cPriceList;
	}

	public void setcPriceList(ArrayList<String> cPriceList) {
		this.cPriceList = cPriceList;
	}

	public ArrayList<String> getcStatusList() {
		return cStatusList;
	}

	public void setcStatusList(ArrayList<String> cStatusList) {
		this.cStatusList = cStatusList;
	}

	public ArrayList<String> getcOrderUrlList() {
		return cOrderUrlList;
	}

	public void setcOrderUrlList(ArrayList<String> cOrderUrlList) {
		this.cOrderUrlList = cOrderUrlList;
	}

	public static String getCurrentYear() {
		return CURRENT_YEAR;
	}

	private class TourBrief {
		private String tourArea;
		private String contentUrl;
		private String departCity;

		public TourBrief(String tourArea, String contentUrl, String departCity) {
			this.tourArea = tourArea;
			this.contentUrl = contentUrl;
			this.departCity = departCity;
		}

		public String getDepartCity() {
			return departCity;
		}

		public void setDepartCity(String departCity) {
			this.departCity = departCity;
		}

		public String getContentUrl() {
			return contentUrl;
		}

		public void setContentUrl(String contentUrl) {
			this.contentUrl = contentUrl;
		}

		public String getTourArea() {
			return this.tourArea;
		}

		public void setTourArea(String tourArea) {
			this.tourArea = tourArea;
		}
	}

	public static void main(String[] args) {
		BTCaissaDe caissa = new BTCaissaDe();
		try {
			caissa.getData();
			caissa.printData();
			BusTour.deleteSubjects(Constants.OWNER_DE_CAISSA);
			caissa.updateDbLyouu();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ArrayList<TourBrief> getTourBriefList() {
		return tourBriefList;
	}

	public void setTourBriefList(ArrayList<TourBrief> tourBriefList) {
		this.tourBriefList = tourBriefList;
	}

	public ArrayList<String> getcHighlightList() {
		return cHighlightList;
	}

	public void setcHighlightList(ArrayList<String> cHighlightList) {
		this.cHighlightList = cHighlightList;
	}
}
